#
# Example GeoAlchemy query from existing PostGIS table 
# Frank Purcell
# June 1, 2010
#
import sys
from sqlalchemy     import *
from sqlalchemy.orm import *
from geoalchemy     import *
from sqlalchemy.sql import func

from utils.csvlib   import *

#
# DB connection
# NOTE: set echo=True to see all that *Alchemy is doing to talk to the database

class SimpleTable():

    def __init__(self, name, database = "postgresql://postgres@localhost:5432/postgres"):
        self.engine   = create_engine(database, echo=False)
        self.metadata = MetaData()
        self.name = name

        #
        # define relational object, mapping to db table
        # tell sqlalchemy about the special type of the PostGIS geom column
        # other columns will be mapped automatically from the table's metadata
        #
        self.source = Table(self.name,
                       self.metadata,
                       GeometryExtensionColumn('the_geom', Geometry(2)),
                       autoload=True,
                       autoload_with=self.engine
                 )

        #
        # open a bound session on the database & get a table
        #
        Session = sessionmaker(bind=self.engine)
        session = Session()
        rows = session.query(self.source)

        csv = GeoSearchWriter(self.name + '.csv')
        for r in rows:
            center = DBSpatialElement(r.the_geom.centroid())
            csv.writerow({
                'name' :  r.name,
                'id'   :  self.name + '-' + `r.gid`,
                'type' :  self.name,
                'sub_type' :  None,
                'neighborhood' :  r.name,
                'city' :  r.city,
                'lat'  : session.scalar(center.y),
                'lon'  : session.scalar(center.x),
                'bbox' : session.scalar(DBSpatialElement(r.the_geom.envelope()).wkt),
            })

x = SimpleTable('neighborhoods');